package uscdb.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import uscdb.dao.ProductDaoI;
import uscdb.dao.ProjectDaoI;
import uscdb.model.Tproduct;
import uscdb.model.Tproject;
import uscdb.pageModel.Product;
import uscdb.util.poi.ExcelUtils;
import uscdb.util.poi.JsGridReportBase;
import uscdb.util.poi.TableData;
import uscdb.util.poi.leoUtil;

@Service
public class PoiService {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(PoiService.class);

	@Autowired
	private ProjectDaoI projectDao;

	@Autowired
	private ProductDaoI productDao;

	private FileInputStream fileStream;

	/**
	 * 功能：导出全部的 项目信息
	 * 
	 * @param request
	 * @param response
	 */
	public void exportProjectToExcel(HttpServletRequest request, HttpServletResponse response) {
		List<Tproject> tprojectList = projectDao.find("from Tproject");
		if (null != tprojectList && tprojectList.size() > 0) {
			String title = "项目信息Excel表";
			// 表头数组
			String[] hearders = new String[] { "产品", "___片区___", "项目名称", "操作类型", "___当前版本___", "___目标版本___", "项目类型",
					"计划人力", "项目进度", "项目质量", "实际人力", "___保障PM___", "PMO", "SE/SA接口", "维护接口", "___服务接口___", "___一线接口___",
					"出差支撑人员", "出差人员时间", "项目进展", "项目启动时间", "________现网操作计划________", "________关键进展________",
					"________风险________", "项目记录修改时间" };
			// 属性的字段
			String[] fields = new String[] { "productName", "area", "name", "operationType", "currentVersion",
					"targetVersion", "type", "humanResources", "process", "quality", "activeHumanResources",
					"safeguardPM", "pmo", "seAndSA", "maintainPM", "servicePM", "frontlinePM", "supportPersonnel",
					"supportTime", "progress", "startDateTime", "operationPlan", "keyAdvances", "risk",
					"modifydatetime" };
			TableData td = ExcelUtils.createTableData(tprojectList, ExcelUtils.createTableHeader(hearders), fields);
			JsGridReportBase report;
			try {
				report = new JsGridReportBase(request, response);
				report.exportToExcel(title, "admin", td);
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
			}

		} else {
			return;
		}

	}

	public void importProductExcel(File file) {
		try {
			if (leoUtil.isExcel(file)) {
				fileStream = new FileInputStream(file);
				logger.info("打开新的文件流");
				Workbook book = WorkbookFactory.create(fileStream);
				Sheet sheet = book.getSheetAt(0);
				// 从0开始，第一行对应sheet.getRow(0)
				for (int i = 4; i <= sheet.getLastRowNum(); i++) {
					Row ros = sheet.getRow(i);
					Tproduct tproduct = productDao.getByName(leoUtil.ConvertCellToStr(ros.getCell(0)));
					// 如果非空，即是存在相同的记录，应该更新数据
					if (null != tproduct) {
						// 更新数据
						productDao.update(tproduct);
					} else {
						// 查询的对象为空，即是全新的记录，应该新增
						tproduct.setId(UUID.randomUUID().toString());
						tproduct.setName(leoUtil.ConvertCellToStr(ros.getCell(0)));
						tproduct.setDescription(leoUtil.ConvertCellToStr(ros.getCell(1)));
						// 这里可能需要使用service
						productDao.save(tproduct);
					}
				}

			} else {
				logger.info("不是excel文件");
			}
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			logger.info("importProductExcel() into finally");
			if (fileStream != null) {
				try {
					fileStream.close();
				} catch (IOException e) {
					logger.error(e.getMessage(), e);
				}
			} else {
				logger.info("空Excel表");
			}
		}

	}

}
